﻿using System;
using System.Data.SQLite;

namespace Petroleum.Tool
{
    public static class SQLiterTool
    {

        /// <summary>
        /// 新建数据库文件
        /// </summary>
        /// <param name="dbPath">数据库文件路径及名称</param>
        /// <returns>新建成功，返回true，否则返回false</returns>
        public static Boolean NewDbFile(string dbPath)
        {
            try
            {
                SQLiteConnection.CreateFile(dbPath);
                return true;
            }
            catch (Exception ex)
            {
                throw new Exception("新建数据库文件" + dbPath + "失败：" + ex.Message);
            }
        }


        /// <summary>
        /// 创建表
        /// </summary>
        /// <param name="dbPath">指定数据库文件</param>
        /// <param name="tableName">表名称</param>
        static public void NewTable(string dbPath, string tableName)
        {
            try
            {
                ////第一次出现的位置
                //int a = dbPath.IndexOf(@"\");
                ////移除再插入
                //dbPath = dbPath.Remove(a, 1).Insert(a, "");
                SQLiteConnection sqliteConn = new SQLiteConnection("data source=" + dbPath);
                if (sqliteConn.State != System.Data.ConnectionState.Open)
                {
                    sqliteConn.Open();
                    SQLiteCommand cmd = new SQLiteCommand();
                    cmd.Connection = sqliteConn;
                    cmd.CommandText = "CREATE TABLE " + tableName + "(id varchar,name varchar,value varchar)";
                    cmd.ExecuteNonQuery();
                }
                sqliteConn.Close();
            }
            catch (Exception e)
            {
                throw e;
            }
        }
        static string GetNewTableSql(string tablename)
        {
            string sql = "";
            switch (tablename)
            {
                case "GuiedStatus":
                    sql = "CREATE TABLE " + tablename + "(id varchar,pagename varchar,value varchar)";
                    break;
            }
            return sql;
        }
        /// <summary>
        /// 创建表
        /// </summary>
        /// <param name="dbPath">指定数据库文件</param>
        /// <param name="tableName">表名称</param>
        public static void NewTableLogin(string dbPath, string tableName)
        {
            try
            {
                using (SQLiteConnection sqliteConn = new SQLiteConnection("data source=" + dbPath))
                {
                    if (sqliteConn.State != System.Data.ConnectionState.Open)
                    {
                        sqliteConn.Open();
                        SQLiteCommand cmd = new SQLiteCommand();
                        cmd.Connection = sqliteConn;
                        cmd.CommandText = "CREATE TABLE " + tableName + "(login_id varchar,LoginName varchar,LoginPwd varchar,IsRememberPwd varchar,IsAutoLogin varchar,IsPrintPrescription varchar,LoginDate varchar)";
                        cmd.ExecuteNonQuery();
                    }
                    sqliteConn.Close();
                };
            }
            catch (Exception e)
            {
                //MessageBox.Show(e.Message);
                throw e;
            }
        }
        public static bool IsTableExist(string dbPath, string tablename)
        {
            bool isTableExist = true;
            using (SQLiteConnection sqliteConn = new SQLiteConnection("data source=" + dbPath))
            {
                if (sqliteConn.State != System.Data.ConnectionState.Open)
                {
                    sqliteConn.Open();
                    SQLiteCommand cmd = new SQLiteCommand();
                    cmd.Connection = sqliteConn;
                    cmd.CommandText = "SELECT count(*) FROM sqlite_master WHERE type='table' AND name='" + tablename + "'";
                    int result = Convert.ToInt32(cmd.ExecuteScalar());
                    if (result <= 0)
                        isTableExist = false;
                    else
                        isTableExist = true;
                }
                return isTableExist;
            };
        }
    }
}